/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Object;

import Object.BookProperties.Author;
import Object.BookProperties.Type;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
/**
 *
 * @author PhongLK60473
 */
public class Book implements Serializable{
    protected int BookId;
    protected String BookTitle;
    protected int BookTitleId;
    protected String BookAuthor;
    protected int BookAuthorId;
    protected String BookType;
    protected String Description;
    protected int BookTypeId;
    protected float Price;

    protected Author Author;
    protected Type Type;

    public static Author AuthorObj() {
        return new Author();
    }
    public static Type TypeObj() {
        return new Type();
    }
    public String getBookAuthor() {
        return BookAuthor;
    }

    public Author getAuthor() {
        return Author;
    }

    public void setAuthor(Author Author) {
        this.Author = Author;
    }

    public Type getType() {
        return Type;
    }

    public void setType(Type Type) {
        this.Type = Type;
    }

    public String getDescription() {
        return Description;
    }

    public void setDescription(String Description) {
        this.Description = Description;
    }

    public void setBookAuthor(String BookAuthor) {
        this.BookAuthor = BookAuthor;
    }

    public int getBookAuthorId() {
        return BookAuthorId;
    }

    public void setBookAuthorId(int BookAuthorId) {
        this.BookAuthorId = BookAuthorId;
    }

    public int getBookId() {
        return BookId;
    }

    public void setBookId(int BookId) {
        this.BookId = BookId;
    }

    public String getBookTitle() {
        return BookTitle;
    }

    public void setBookTitle(String BookTitle) {
        this.BookTitle = BookTitle;
    }

    public int getBookTitleId() {
        return BookTitleId;
    }

    public void setBookTitleId(int BookTitleId) {
        this.BookTitleId = BookTitleId;
    }

    public String getBookType() {
        return BookType;
    }

    public void setBookType(String BookType) {
        this.BookType = BookType;
    }

    public int getBookTypeId() {
        return BookTypeId;
    }

    public void setBookTypeId(int BookTypeId) {
        this.BookTypeId = BookTypeId;
    }

    public float getPrice() {
        return Price;
    }

    public void setPrice(float Price) {
        this.Price = Price;
    }

    public static String displaySearchBook(String strSearch,int searchBy){
        String strHTML="Search "+strSearch+" in type : " + searchBy+"<br>";

        Book[] books = searchBook(strSearch, searchBy);
        for(int i=0;i<books.length;i++){
            strHTML+="<br><b>Title : </b>"+books[i].BookTitle+"<br>";
            strHTML+="<b>Description : </b>"+books[i].Description+"<br>";
            strHTML+="<b>Author : </b>"+books[i].BookAuthor+"<br>";
            strHTML+="<b>Type : </b>"+books[i].BookType+"<br>";
        }

        return strHTML;
    }

    public static Book[] searchBook(String strSearch,int searchBy){
        /*
         * @searchBy    0 : title ; 1 Author ; 2 : BookTypes;
         */
        ArrayList alBooks = new ArrayList();

        boolean debug=true;
        Database db = new Database();

        if(db.isOK){
            if(debug){//Debug
                System.out.println("strSearch : " + strSearch);
                System.out.println("searchBy : " + searchBy);
            }
            String strSQL;
            PreparedStatement stm=null;
            ResultSet rs = null;
            try {
                
            if(searchBy==0){
                strSQL = "SELECT id FROM Books WHERE title LIKE ? ";
                stm = db.con.prepareStatement(strSQL);
                stm.setString(1, strSearch);
            } else if (searchBy == 1){
                strSQL = "SELECT id FROM Authors WHERE name LIKE ?";
                stm = db.con.prepareStatement(strSQL);
                stm.setString(1, strSearch);
                rs = stm.executeQuery();
                
                
                ArrayList authorids = new ArrayList();
                while(rs.next()){
                    authorids.add(rs.getInt(1)+"");
                }
                String strSQL2="SELECT id FROM Books WHERE authorid IN (";
                for(int i=0;i<authorids.size();i++){
                    if(i!=0){
                        strSQL2+=",";
                    }
                    strSQL2+="?";
                }
                strSQL2+=")";
                stm = db.con.prepareStatement(strSQL2);
                for(int i=0;i<authorids.size();i++){
                    stm.setInt(i+1, Integer.parseInt(authorids.get(i).toString()));
                }
            } else if ( searchBy == 2){
                strSQL = "SELECT id FROM BookTypes WHERE name LIKE ?";
                stm = db.con.prepareStatement(strSQL);
                stm.setString(1, strSearch);
                rs = stm.executeQuery();


                ArrayList typeids = new ArrayList();
                while(rs.next()){
                    typeids.add(rs.getInt(1)+"");
                }
                String strSQL2="SELECT id FROM Books WHERE typeid IN (";
                for(int i=0;i<typeids.size();i++){
                    if(i!=0){
                        strSQL2+=",";
                    }
                    strSQL2+="?";
                }
                strSQL2+=")";
                stm = db.con.prepareStatement(strSQL2);
                for(int i=0;i<typeids.size();i++){
                    stm.setInt(i+1, Integer.parseInt(typeids.get(i).toString()));
                }
            } else {
                return null;
            }
                rs = stm.executeQuery();

                while(rs.next()){

                    Book book = new Book(rs.getInt(1));
                    alBooks.add(book);
                    if(debug){
                        System.out.println("book+"+book.BookTitle);
                    }
                }
            } catch (SQLException e) {
                System.out.println("Order(){ SQL : "+e.getMessage());
            } finally {
                try{
                    rs.close();
                    stm.close();
                    db.con.close();
                } catch (SQLException e){

                }
            }
        }else{
            System.out.println("Order(){ Connect database failed }");
        }

        Book[] aBooks = new Book[alBooks.size()];
        alBooks.toArray(aBooks);
        return aBooks;
    }

    public Book(String BookTitle, int BookAuthorId, String Description, int BookTypeId, float Price) {
        this.BookTitle = BookTitle;
        this.BookAuthorId = BookAuthorId;
        this.Description = Description;
        this.BookTypeId = BookTypeId;
        this.Price = Price;
    }


    public Book(int BookId, String BookTitle, int BookAuthorId, String Description, int BookTypeId, float Price) {
        this.BookId = BookId;
        this.BookTitle = BookTitle;
        this.BookAuthorId = BookAuthorId;
        this.Description = Description;
        this.BookTypeId = BookTypeId;
        this.Price = Price;
    }

    public Book(int BookId,boolean dummy) {
        if(dummy){
            this.BookId = BookId;
        }
    }
    public Book(int BookId) {
        boolean debug=false;
        this.BookId = BookId;
        Database db = new Database();
        if(db.isOK){
            if(debug){//Debug
                System.out.println("Requset id : " + BookId);
            }

            PreparedStatement stm = null;
            ResultSet rs = null;

            String strSQL = "SELECT * FROM Books WHERE id = ? ";
            try {
                stm = db.con.prepareStatement(strSQL);
                stm.setInt(1, BookId);
                rs = stm.executeQuery();
                if(rs.next()){
                    this.BookTitle = rs.getString("title");
                    this.Description = rs.getString("description");
                    this.Price = rs.getFloat("price");
                    
                    this.BookAuthorId = rs.getInt("authorid");
                    this.BookTypeId = rs.getInt("typeid");

                    strSQL = "SELECT name FROM Authors WHERE id = ? ";
                    stm = db.con.prepareStatement(strSQL);
                    stm.setInt(1, this.BookAuthorId);
                    rs = stm.executeQuery();
                    if(rs.next()){
                        this.BookAuthor = rs.getString(1);
                        this.Author = new Author(this.BookAuthorId, this.BookAuthor);
                    } else {
                        System.out.println("Order(){ Select Author has no row }");
                    }

                    strSQL = "SELECT name FROM BookTypes WHERE id = ? ";
                    stm = db.con.prepareStatement(strSQL);
                    stm.setInt(1, this.BookTypeId);
                    rs = stm.executeQuery();

                    if(rs.next()){
                        this.BookType = rs.getString(1);
                        this.Type = new Type(BookTypeId, BookType);
                        //this.Type = new Type(this.BookTypeId, this.BookType);
                        
                    } else {
                        System.out.println("Order(){ Select BookType has no row }");
                    }

                    if(debug){//Debug
                        System.out.println("BookTitle : " + this.BookTitle);
                        System.out.println("BookAuthor : " + this.BookAuthor);
                        System.out.println("BookType : " + this.BookType);
                        System.out.println("Price : " + this.Price);
                    }
                }else{
                    System.out.println("Order(){ Select Book has no row }");
                }
            } catch (SQLException e) {
                System.out.println("Order(){ SQL : "+e.getMessage());
            } finally {
                try{
                    rs.close();
                    stm.close();
                    db.con.close();
                } catch ( SQLException e){
                    
                }
            }
        }else{
            System.out.println("Order(){ Connect database failed }");
        }
    }
    public static boolean SaveBook (HttpServletRequest request){
        boolean rt=false;
        try{
            int id=Integer.parseInt(request.getParameter("bookid"));
            String title = request.getParameter("txtTitle").toString()+"";
            String description = request.getParameter("txtDescription").toString();
            int author = Integer.parseInt(request.getParameter("cbAuthor"));
            int type = Integer.parseInt(request.getParameter("cbType"));
            float price = Float.parseFloat(request.getParameter("txtPrice"));

            Book book = new Book(id, title, author, description, type, price);
            rt=book.Save();
        }catch(Exception e){
            rt=false;
            e.printStackTrace();
        }
        if(!rt){
            Page.setMessage("Cannot save book!", request);
        }else{
            Page.setMessage("Save book successfull!", request);
        }
        return rt;
    }
    public static boolean AddBook(HttpServletRequest request){
        boolean rt=false;
        try{
            String title = request.getParameter("txtTitle").toString()+"";
            String description = request.getParameter("txtDescription").toString();
            int author = Integer.parseInt(request.getParameter("cbAuthor"));
            int type = Integer.parseInt(request.getParameter("cbType"));
            float price = Float.parseFloat(request.getParameter("txtPrice"));

            Book book = new Book(title, author, description, type, price);
            rt=book.Add();
        }catch(Exception e){
            rt=false;
            e.printStackTrace();
        }
        if(!rt){
            Page.setMessage("Add book failed!", request);
        }else{
            Page.setMessage("Add book successfull!", request);
        }
        return rt;
    }
    public static boolean DeleteBook(HttpServletRequest request){
        boolean rt=false;
        try{
            int id=Integer.parseInt(request.getParameter("bookid"));
            Book book = new Book(id);
            
            rt=book.Delete();
        }catch(Exception e){
            rt=false;
            e.printStackTrace();
        }
        if(!rt){
            Page.setMessage("Error while delete book!", request);
        }else{
            Page.setMessage("Delete book success!", request);
        }
        return rt;
    }
    public boolean Add(){
        boolean rt=false;

        Database db = new Database();

        if(db.isOK){
            String strSQL;
            PreparedStatement stm=null;
            int rs;
            try {
                strSQL = "INSERT INTO Books (title,description,authorid,typeid"
                        + ",price) VALUES(?, ?, ?, ?, ?)";
                stm = db.con.prepareStatement(strSQL);
                stm.setString(1, this.BookTitle);
                stm.setString(2, this.Description);
                stm.setInt(3, this.getBookAuthorId());
                stm.setInt(4, this.getBookTypeId());
                stm.setFloat(5, this.getPrice());
                

                rs = stm.executeUpdate();

                if(rs>0){
                    rt=true;
                }else{
                    System.out.println("AddBook() { No row affected!");
                    rt=false;
                }
            } catch (SQLException e) {
                System.out.println("AddBook(){ SQL : "+e.getMessage());
                rt=false;
            } finally {
                try{
//                   rs.close();
                    stm.close();
                    db.con.close();
                } catch (SQLException e){

                }
            }
        }else{
            rt=false;
            System.out.println("AddBook(){ Connect database failed }");
        }

        return rt;
    }
    public boolean Delete(){
        boolean rt=false;

        Database db = new Database();

        if(db.isOK){
            String strSQL;
            PreparedStatement stm=null;
            int rs;
            try {
                strSQL = "DELETE FROM Books "
                        + " WHERE id = ?";
                stm = db.con.prepareStatement(strSQL);
                stm.setInt(1, this.BookId);

                rs = stm.executeUpdate();

                if(rs>0){
                    rt=true;
                }else{
                    rt=false;
                }
            } catch (SQLException e) {
                System.out.println("Delete(){ SQL : "+e.getMessage());
                rt=false;
            } finally {
                try{
//                    rs.close();
                    stm.close();
                    db.con.close();
                } catch (SQLException e){

                }
            }
        }else{
            rt=false;
            System.out.println("Delete(){ Connect database failed }");
        }

        return rt;
    }
    public boolean Save(){
        boolean rt=false;

        Database db = new Database();

        if(db.isOK){
            String strSQL;
            PreparedStatement stm=null;
            int rs;
            try {
                strSQL = "UPDATE Books SET title = ? "
                        + ", description = ? "
                        + ", authorid = ? "
                        + ", typeid = ?"
                        + ", price = ? "
                        + " WHERE id = ?";
                stm = db.con.prepareStatement(strSQL);
                stm.setString(1, this.BookTitle);
                stm.setString(2, this.Description);
                stm.setInt(3, this.getBookAuthorId());
                stm.setInt(4, this.getBookTypeId());
                stm.setFloat(5, this.getPrice());
                stm.setInt(6, this.BookId);
                
                rs = stm.executeUpdate();

                if(rs>0){
                    rt=true;
                }else{
                    rt=false;
                }
            } catch (SQLException e) {
                System.out.println("SaveBook(){ SQL : "+e.getMessage());
                rt=false;
            } finally {
                try{
//                    rs.close();
                    stm.close();
                    db.con.close();
                } catch (SQLException e){
                    
                }
            }
        }else{
            rt=false;
            System.out.println("SaveBook(){ Connect database failed }");
        }

        return rt;
    }
    

}
